﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.IO;

namespace SimpleSM
{
    class DB
    {
        #region Variables
        private string dbConnection = string.Format("Data Source={0};foreign keys=true;", Properties.Settings.Default.database);
        private bool open = false;
        private SQLiteConnection cnn;
        private SQLiteCommand mycommand;
        private SQLiteDataReader reader;
        #endregion

        public DB()
        {
            this.Connect();
        }

        #region Connection/disconnection DB
        public void Connect()
        {
            try
            {
                if (Directory.Exists(Path.GetDirectoryName(Properties.Settings.Default.database)) == false)
                {
                    Directory.CreateDirectory(Path.GetDirectoryName(Properties.Settings.Default.database));
                }
                this.cnn = new SQLiteConnection(dbConnection);
                this.cnn.Open();
                this.open = true;
            }
            catch (Exception e)
            {
                this.open = false;
                throw new Exception(e.Message);
            }
        }

        public void Connect(string file)
        {
            try
            {
                string filedb = string.Format("Data Source={0};", file);
                this.cnn = new SQLiteConnection(filedb);
                if (System.IO.File.Exists(file) == false)
                {
                    this.cnn.CreateCommand();
                }
                this.cnn.Open();
                this.open = true;
            }
            catch (Exception e)
            {
                this.open = false;
                throw new Exception(e.Message);
            }
        }

        public void Disconnect()
        {
            this.cnn.Close();
        }
        #endregion

        #region Functions
        public DataTable Select(string sql, string[,] param = null)
        {
            DataTable dt = new DataTable();
            if (this.open)
            {
                this.mycommand = new SQLiteCommand(cnn);
                this.mycommand.CommandType = CommandType.Text;
                this.mycommand.Parameters.Clear();
                if (param != null)
                {
                    for (int i = 0; i < (param.Length / 2); i++)
                    {
                        this.mycommand.Parameters.Add(new SQLiteParameter(param[i, 0].ToString(), param[i, 1]));
                    }
                }
                this.mycommand.CommandText = sql;
                try
                {
                    this.reader = this.mycommand.ExecuteReader();
                    dt.Load(reader);
                    this.reader.Close();
                }
                catch (ConstraintException ex)
                {
                    //string x = ex.ToString();
                    File.AppendAllLines(AppDomain.CurrentDomain.BaseDirectory + "sqlerror.txt", (DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + " " + ex.ToString()).Split('\n'));
                }
                finally
                {

                }
            }
            return dt;
        }

        public int Count(string sql, string[,] param = null)
        {
            object i0 = "";
            if (this.open)
            {
                this.mycommand = new SQLiteCommand(cnn);
                this.mycommand.Parameters.Clear();
                if (param != null)
                {
                    for (int i = 0; i < (param.Length / 2); i++)
                    {
                        this.mycommand.Parameters.Add(new SQLiteParameter(param[i, 0].ToString(), param[i, 1]));
                    }
                }
                this.mycommand.CommandText = sql;
                i0 = this.mycommand.ExecuteScalar();
            }
            int i1 = 0;
            if (i0.ToString() != "")
            {
                i1 = int.Parse(i0.ToString());
            }
            return i1;
        }

        public bool NonSelect(string sql, string[,] param = null)
        {
            bool b = false;
            if (this.open)
            {
                try
                {
                    this.mycommand = new SQLiteCommand(cnn);
                    this.mycommand.CommandType = CommandType.Text;
                    this.mycommand.Parameters.Clear();
                    if (param != null)
                    {
                        for (int i = 0; i < (param.Length / 2); i++)
                        {
                            this.mycommand.Parameters.Add(new SQLiteParameter(param[i, 0].ToString(), param[i, 1]));
                        }
                    }
                    this.mycommand.CommandText = sql;
                    this.mycommand.ExecuteNonQuery();
                    b = true;
                }
                catch
                {
                    b = false;
                }
            }
            return b;
        }

        public int LastInsertId()
        {
            return int.Parse(this.Select("SELECT last_insert_rowid();").Rows[0][0].ToString());
        }
        #endregion

        #region InitDB
        public void InitDB()
        {
            this.NonSelect(Properties.Resources.initdb);
        }
        #endregion

        #region Update
        public bool ColumnExists(string table, string column)
        {
            bool b = true;
            DataTable dt = this.Select("PRAGMA table_info(" + table + ");");
            if (dt.Columns.Contains(column) == false)
            {
                b = false;
            }
            return b;
        }

        public void Update()
        {
            if (this.ColumnExists("sessions", "info") == false)
            {
                this.NonSelect("ALTER TABLE sessions ADD COLUMN info TEXT;");
            }
        }
        #endregion
    }
}
